package com.example.graduationdesign.mapper;


import cn.hutool.extra.tokenizer.Word;
import com.example.graduationdesign.pojo.dao.*;
import com.example.graduationdesign.pojo.dto.*;
import com.example.graduationdesign.pojo.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;
import java.util.Map;

/**
 * 实现用户端用户行为接口的数据库操作代码
 */
@Mapper
public interface ConsumerMapper {

    /**
     * 使用用户所处的阶段获取该阶段所需要学习的单词个数
     * @param wordStage
     * @return
     */
    @Select("select code, word, phonetic, pronunciation,interpretation,split,synthesis,associative,example,translate,word_difficulty,word_stage,is_studyed,is_familarized from `wordlist` where word_stage = #{wordStage};")
    List<WordDao> queryWordByWordStage(@Param("wordStage") Integer wordStage);


    /**
     * 用于接收客户端在学习英语时前端传回的数据并将其写入数据库中
     * @param userStatusStudyDao
     */
    Integer AddUserStatusStudy(@Param("userStatusStudyDao") UserStatusStudyDao userStatusStudyDao);


    /**
     * 用于在studyrecord表中查找是否已经存在该条学习记录
     * @param userStatusStudyDao
     * @return
     */
    @Select("select studyrecord_code, studyrecord_consumer_id from `studyrecord` where studyrecord_code = #{userStatusStudyDao.code} AND studyrecord_consumer_id = #{userStatusStudyDao.consumerId};")
    StudyRecordDto IsExistThisStudyRecord(@Param("userStatusStudyDao") UserStatusStudyDao userStatusStudyDao);

    /**
     * 更新用户的学习状态数据
     * @param userStatusStudyDao
     * @return
     */
    @Update("update studyrecord set studyed_is_studyed = #{userStatusStudyDao.isStudyed},studyed_is_familarized = #{userStatusStudyDao.isFamilarized},studyrecord_time = #{userStatusStudyDao.createTime} WHERE studyrecord_code =#{userStatusStudyDao.code} AND studyrecord_consumer_id = #{userStatusStudyDao.consumerId}; ")
    Integer UpdateUserStatusStudy(@Param("userStatusStudyDao") UserStatusStudyDao userStatusStudyDao);


    /**
     * 用于查找studyrecord表中该用户对该单词的所有行为
     * @param studyRecordDto
     * @return
     */
    @Select("select studyrecord_consumer_id as consumerId,studyrecord_code as code,studyed_is_studyed as isStudyed,studyed_is_familarized as isFamilarized,studyed_is_like as isLike, studyed_is_favorite as isFavorite from studyrecord WHERE studyrecord_consumer_id = #{studyRecordDto.studyrecordConsumerId} and studyrecord_code = #{studyRecordDto.studyrecordCode};")
    UserStatusStudyDao QueryDataStudyRecord(@Param("studyRecordDto") StudyRecordDto studyRecordDto);


    /**
     * 用户点赞之后更新数据库
     * @param likeOrFavoriteDao
     * @return
     */
    @Update("update studyrecord set studyed_is_like = #{likeOrFavoriteDao.isLike} WHERE studyrecord_code =#{likeOrFavoriteDao.code} AND studyrecord_consumer_id = #{likeOrFavoriteDao.consumerId}; ")
    Integer UpdateUserStatusLike(@Param("likeOrFavoriteDao") LikeOrFavoriteDao likeOrFavoriteDao);


    /**
     * 用户收藏之后更新数据库
     * @param likeOrFavoriteDao
     * @return
     */
    @Update("update studyrecord set studyed_is_favorite = #{likeOrFavoriteDao.isFavorite} WHERE studyrecord_code =#{likeOrFavoriteDao.code} AND studyrecord_consumer_id = #{likeOrFavoriteDao.consumerId} ")
    Integer UpdateUserStatusFavorite(@Param("likeOrFavoriteDao") LikeOrFavoriteDao likeOrFavoriteDao);


    /**
     * 获取用户打卡的题目数据
     * @param consumerStage
     * @param consumerId
     * @return
     */
//    下述SQL的作用：
//
//    避免重复推荐：
//
//    通过LEFT JOIN连接studyrecord和testrecord表，确保不会重复推荐已掌握的单词
//
//    使用条件(tr.testrecord_is_mastered = 0 OR tr.testrecord_is_mastered IS NULL)排除已掌握的单词
//
//    优先级排序：
//
//    首先推荐最近30天内错误的单词（testrecord_is_true = 0）
//
//    其次推荐不熟悉的单词（studyed_is_familarized = 0）
//
//    最后推荐其他符合条件的单词
//
//    随机性：
//
//    在每个优先级组内使用RAND()函数进行随机排序
//
//    确保推荐的多样性
//
//    阶段匹配：
//
//    仍然保持按用户当前阶段筛选单词（word_stage = #{consumerStage}）
//
//    这个查询逻辑与WordRecommendationSystem算法的主要思想保持一致，包括：
//
//            优先推荐错误单词
//
//            考虑单词熟悉度
//
//            避免推荐已掌握的单词
//
//            按阶段筛选单词
//
//    同时保持了原始查询的基本结构和返回字段。
    @Select("SELECT \n" +
            "    w.code, \n" +
            "    w.word, \n" +
            "    w.translate AS zn, \n" +
            "    w.example AS en, \n" +
            "    w.interpretation AS definition \n" +
            "FROM \n" +
            "    wordlist w\n" +
            "WHERE \n" +
            "    w.word_stage = #{consumerStage} \n" +
            "    AND w.code IN (\n" +
            "        SELECT DISTINCT w2.code\n" +
            "        FROM wordlist w2\n" +
            "        LEFT JOIN studyrecord sr ON sr.studyrecord_code = w2.code AND sr.studyrecord_consumer_id = #{consumerId}\n" +
            "        LEFT JOIN testrecord tr ON tr.testrecord_code = w2.code AND tr.testrecord_consumer_id = #{consumerId}\n" +
            "        WHERE (sr.studyed_is_studyed = 1 OR sr.studyed_is_studyed IS NULL)\n" +
            "        AND (tr.testrecord_is_mastered = 0 OR tr.testrecord_is_mastered IS NULL)\n" +
            "    )\n" +
            "ORDER BY \n" +
            "    CASE \n" +
            "        WHEN EXISTS (\n" +
            "            SELECT 1 FROM testrecord tr \n" +
            "            WHERE tr.testrecord_code = w.code \n" +
            "            AND tr.testrecord_consumer_id = #{consumerId}\n" +
            "            AND tr.testrecord_is_true = 0 \n" +
            "            AND tr.testrecord_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)\n" +
            "        ) THEN 0 \n" +
            "        WHEN NOT EXISTS (\n" +
            "            SELECT 1 FROM studyrecord sr \n" +
            "            WHERE sr.studyrecord_code = w.code \n" +
            "            AND sr.studyrecord_consumer_id = #{consumerId}\n" +
            "            AND sr.studyed_is_familarized = 1\n" +
            "        ) THEN 1 \n" +
            "        ELSE 2 \n" +
            "    END, \n" +
            "    RAND() \n" +
            "LIMIT 5;")
    List<QuestionCheckDao> GetCheckQuestion(@Param("consumerStage") Integer consumerStage, @Param("consumerId") Long consumerId);


    /**
     * 查询单词的难度
     * @param wordCode
     * @return
     */
    @Select("select word_difficulty from wordlist where code = #{wordCode}")
    Integer GetWordDifficulty(@Param("wordCode") Long wordCode);


    /**
     * 用于将前端的打卡记录插入至数据库中的数据库操作语言
     * @param checkRecordDao
     * @return
     */
    @Insert("insert into checkrecord(checkrecord_code,checkrecord_time,checkrecord_is_true,checkrecord_consumer_id,checkrecord_style,user_answer,user_question,right_answer) values (#{checkRecordDao.checkrecordCode},#{checkRecordDao.checkrecordTime},#{checkRecordDao.checkrecordIsTrue},#{checkRecordDao.checkrecordConsumerId},#{checkRecordDao.checkrecordStyle},#{checkRecordDao.userAnswer},#{checkRecordDao.userQuestion},#{checkRecordDao.rightAnswer});" )
    Integer InsertCheckRecord(@Param("checkRecordDao") CheckRecordDao checkRecordDao);





    /**
     * 用于获取该用户今日是否打卡的情况
     * @param consumerId
     * @return
     */
    @Select("SELECT COUNT(*) FROM checkrecord WHERE DATE(checkrecord_time) = CURDATE() AND checkrecord_consumer_id = #{consumerId};")
    Integer GetIsChecked(@RequestParam("consumerId") Long consumerId);


    /**
     * 用于获取该用户的全部打卡记录
     * @param consumerId
     * @return
     */
    @Select("SELECT DATE(checkrecord_time) AS checkrecordTime,checkrecord.* FROM checkrecord WHERE checkrecord_consumer_id = #{consumerId};")
    List<CheckRecordDao> GetCheckRecords(@RequestParam("consumerId") Long consumerId);


    /**
     * 用于用户删除某天的打卡记录
     * @param checkRecordDao
     * @return
     */
    @Delete("DELETE FROM checkrecord WHERE checkrecord_consumer_id = #{checkRecordDao.checkrecordConsumerId} and checkrecord_time = #{checkRecordDao.checkrecordTime};")
    Integer DeleteCheckRecord(@Param("checkRecordDao") CheckRecordDao checkRecordDao);


    /**
     * 用于获取用户测试的题目
     * @param wordStage
     * @return
     */
    @Select("SELECT * \n" +
            "FROM wordlist\n" +
            "WHERE word_stage=#{wordStage}\n" +
            "ORDER BY RAND()\n" +
            "LIMIT 10;")
    List<WordDao> GetTestWord(@Param("wordStage") Integer wordStage);


    /**
     * 用于获取某个用户在某个阶段的所已经掌握的单词数量
     * @param consumerId
     * @param consumerStage
     * @return
     */
    @Select("SELECT COUNT(*)\n" +
            "FROM studyrecord,wordlist\n" +
            "WHERE studyrecord_code = code AND studyrecord_consumer_id = #{consumerId} AND studyed_is_familarized = 1 AND word_stage = #{consumerStage} ;")
    Integer GetFamilarNumber(@Param("consumerId") Long consumerId,@Param("consumerStage") Integer consumerStage);


    /**
     * 用于获取某个用户在某个阶段的所喜欢的单词数量
     * @param consumerId
     * @param consumerStage
     * @return
     */
    @Select("SELECT COUNT(*)\n" +
            "FROM studyrecord,wordlist\n" +
            "WHERE studyrecord_code = code AND studyrecord_consumer_id = #{consumerId} AND studyed_is_like = 1 AND word_stage = #{consumerStage} ;")
    Integer GetLikeNumber(@Param("consumerId") Long consumerId,@Param("consumerStage") Integer consumerStage);


    /**
     * 用于获取某个用户在某个阶段的所收藏的单词数量
     * @param consumerId
     * @param consumerStage
     * @return
     */
    @Select("SELECT COUNT(*) FROM studyrecord,wordlist WHERE studyrecord_code = code AND studyrecord_consumer_id = #{consumerId} AND studyed_is_favorite = 1 AND word_stage =#{consumerStage};")
    Integer GetFavoriteNumber(@Param("consumerId") Long consumerId,@Param("consumerStage") Integer consumerStage);


    /**
     * 用于设置文章的阅读量
     * @param articleId
     * @return
     */
    @Update("UPDATE article set article_number = article_number+1 WHERE article_code = #{articleId};")
    Integer SetArticleReadNumber(@Param("articleId") Long articleId);


    /**
     * 用于获取用户进行默写的单词
     * @param userId
     * @param userStage
     * @return
     */
    @Select("select wordlist.* from wordlist,studyrecord where code = studyrecord_code AND studyrecord_consumer_id = #{userId} AND word_stage = #{userStage} AND studyed_is_familarized = 1;")
    List<WordDao> GetReciteWord(@Param("userId") Long userId,@Param("userStage") Integer userStage);


    /**
     * 用于获取用户的打卡积分情况
     * @param userId
     * @return
     */
    @Select("SELECT \n" +
            "    SUM(CASE \n" +
            "        WHEN w.word_difficulty = 1 THEN 2\n" +
            "        WHEN w.word_difficulty = 2 THEN 4\n" +
            "        WHEN w.word_difficulty = 3 THEN 6\n" +
            "        ELSE 0\n" +
            "    END) AS totalScore\n" +
            "FROM \n" +
            "    checkrecord cr\n" +
            "JOIN \n" +
            "    wordlist w ON cr.checkrecord_code = w.code\n" +
            "WHERE \n" +
            "    cr.checkrecord_consumer_id = #{userId}\n" +
            "    AND DATE(cr.checkrecord_time) < CURDATE()\n" +  // 修改为今天之前的数据
            "    AND cr.checkrecord_is_true = 1;  -- 假设有一个字段 isCorrect 表示题目是否正确")
    Integer GetTotalScore(@Param("userId") Long userId);


    /**
     * 更新用户的打卡积分情况
     * @param userId
     * @param totalScore
     * @return
     */
    @Update("UPDATE consumer \n" +
            "SET consumer_score = IFNULL(consumer_score, 0) + IFNULL(#{totalScore},0) \n" +
            "WHERE consumer_id = #{userId};")
    Integer SetConsumerScore(@Param("userId") Long userId,@Param("totalScore") Integer totalScore);


    /**
     * 删除经过计算后的打卡数据记录
     * @param userId
     * @return
     */
    @Delete("DELETE FROM checkrecord\n" +
            "WHERE DATE(checkrecord_time) < CURDATE() AND checkrecord_consumer_id = #{userId};")
    Integer DeleteCheckRecordBeforeToday(@Param("userId") Long userId);


    /**
     * 用于获取用户的积分
     * @param userId
     * @return
     */
    @Select("select consumer_score from user,consumer where consumer_id = user_id and user_id = #{userId};")
    Long GetScore(@Param("userId") Long userId);


    /**
     * 用于实现获取用户读书笔记的SQL实现
     * @param userId
     * @return
     */
    @Select("select * from notebook where note_consumer_id  = #{userId}")
    List<NoteBookDao> GetNoteBook(@Param("userId") Long userId);


    /**
     * 用于实现用户增加读书笔记的情况
     * @param userId
     * @param title
     * @param content
     * @return
     */
    @Insert("insert into notebook(note_consumer_id,note_content,note_title) values (#{userId},#{content},#{title});")
    Integer AddNoteBook(@Param("userId") Long userId,@Param("title") String title,@Param("content") String content);


    /**
     * 用于实现更新用户笔记的功能
     * @param userId
     * @param code
     * @param content
     * @param title
     * @return
     */
    @Update("update notebook set note_content = #{content},note_title = #{title},update_time = NOW() where note_code = #{code} and note_consumer_id = #{userId}; ")
    Integer UpdateNoteBook(@Param("userId")Long userId,@Param("code")Long code,@Param("content")String content,@Param("title")String title);


    /**
     * 用于实现用户对读书笔记的删除操作
     * @param noteCode
     * @return
     */
    @Delete("DELETE from notebook where note_code = #{noteCode};")
    Integer DeleteNoteBook(@Param("noteCode") Long noteCode);


    /**
     * 用于获取用户的排名
     *
     * @param userStage
     * @return
     */
    @Select("SELECT\n" +
            "    consumer_id,\n" +
            "    consumer_score,\n" +
            "    consumer_stage,\n" +
            "    RANK() OVER (PARTITION BY consumer_stage ORDER BY consumer_score DESC) AS user_rank  -- 使用user_rank作为别名\n" +
            "FROM\n" +
            "    consumer\n" +
            "WHERE\n" +
            "        consumer_stage = #{userStage}  -- 替换为你想查询的阶段\n" +
            "ORDER BY\n" +
            "    user_rank;  -- 使用user_rank排序")
    List<RankVo> GetRank(@Param("userStage") Integer userStage);


    /**
     * 用于获取用户的专属词库
     * @param userID
     * @param userStage
     * @return
     */
    @Select("SELECT \n" +
            "    w.code,\n" +
            "    w.word,\n" +
            "    w.phonetic,\n" +
            "    w.pronunciation,\n" +
            "    w.interpretation,\n" +
            "    w.split,\n" +
            "    w.synthesis,\n" +
            "    w.associative,\n" +
            "    w.example,\n" +
            "    w.translate,\n" +
            "    w.word_difficulty,\n" +
            "    w.word_stage,\n" +
            "    CASE \n" +
            "        WHEN sr.studyrecord_code IS NOT NULL THEN sr.studyed_is_studyed\n" +
            "        ELSE w.is_studyed\n" +
            "    END AS is_studyed,\n" +
            "    CASE \n" +
            "        WHEN sr.studyrecord_code IS NOT NULL THEN sr.studyed_is_familarized\n" +
            "        ELSE w.is_familarized\n" +
            "    END AS is_familarized,\n" +
            "    CASE \n" +
            "        WHEN sr.studyrecord_code IS NOT NULL THEN sr.studyed_is_like\n" +
            "        ELSE 0\n" +
            "    END AS is_liked,\n" +
            "    CASE \n" +
            "        WHEN sr.studyrecord_code IS NOT NULL THEN sr.studyed_is_favorite\n" +
            "        ELSE 0\n" +
            "    END AS is_favorite,\n" +
            "    w.create_time,\n" +
            "    w.update_time\n" +
            "FROM \n" +
            "    wordlist w\n" +
            "LEFT JOIN \n" +
            "    studyrecord sr ON w.code = sr.studyrecord_code \n" +
            "                   AND sr.studyrecord_consumer_id = #{userId}\n" +
            "WHERE \n" +
            "    w.word_stage = #{userStage}\n" +
            "ORDER BY \n" +
            "    w.code;")
    List<WordPersonalVo> GetAllWords(@Param("userId") Long userID, @Param("userStage") Integer userStage);




    /**
     * 用于获取轮播图数据
     * @return
     */
    @Select("SELECT article_code as articleId, article_image as articleImage, article_name as articleName \n" +
            "FROM article \n" +
            "WHERE article_image IS NOT NULL AND article_image != ''\n" +
            "ORDER BY article_number DESC \n" +
            "LIMIT 3;")
    List<BannersDto> GetBanners();


    /**
     * 用于获取用户的积分排名
     * @return
     */
    @Select("SELECT \n" +
            "    u.user_name AS name,\n" +
            "    c.consumer_score AS score\n" +
            "FROM \n" +
            "    user u\n" +
            "JOIN \n" +
            "    consumer c ON u.user_id = c.consumer_id,\n" +
            "    (SELECT @curRank := 0) r\n" +
            "WHERE \n" +
            "    c.consumer_stage = #{stage} and u.role_id != 102\n" +
            "ORDER BY \n" +
            "    c.consumer_score DESC\n" +
            "LIMIT 6")
    List<ConsumerScoreDto> GetConsumerScoreRank(@Param("stage") Integer stage);


    /**
     * 获取某个阶段用户的学习进度的排名
     */
    @Select("SELECT \n" +
            "    u.user_name AS name,\n" +
            "    COUNT(DISTINCT sr.studyrecord_code) AS progress\n" +
            "FROM \n" +
            "    user u\n" +
            "JOIN \n" +
            "    consumer c ON u.user_id = c.consumer_id\n" +
            "LEFT JOIN \n" +
            "    studyrecord sr ON u.user_id = sr.studyrecord_consumer_id\n" +
            "    AND sr.studyed_is_familarized = 1\n" +
            "LEFT JOIN \n" +
            "    wordlist w ON sr.studyrecord_code = w.code\n" +
            "    AND w.word_stage = #{stage}\n" +
            "CROSS JOIN\n" +
            "    (SELECT @curRank := 0) r\n" +
            "WHERE \n" +
            "    c.consumer_stage = #{stage}  AND u.role_id != 102 \n" +
            "GROUP BY \n" +
            "    u.user_id, u.user_name\n" +
            "ORDER BY \n" +
            "    progress DESC\n" +
            "LIMIT 6;")
    List<ProgressRankDto> GetProgressRank(@Param("stage") Integer stage);


    /**
     * 用于获取用户在某个阶段的学习数据
     * @param userId 用户ID
     * @param stage 学习阶段
     * @return 学习进度数据
     */
    @Select("SELECT \n" +
            "  (SELECT COUNT(DISTINCT DATE(sr.studyrecord_time)) \n" +
            "   FROM studyrecord sr\n" +
            "   JOIN wordlist w ON sr.studyrecord_code = w.code\n" +
            "   WHERE sr.studyrecord_consumer_id = #{userId} \n" +
            "   AND w.word_stage = #{stage}\n" +
            "   AND YEARWEEK(sr.studyrecord_time, 1) = YEARWEEK(CURDATE(), 1)) AS weekStudy,\n" +
            "  \n" +
            "  (SELECT COUNT(DISTINCT sr.studyrecord_code) \n" +
            "   FROM studyrecord sr\n" +
            "   JOIN wordlist w ON sr.studyrecord_code = w.code\n" +
            "   WHERE sr.studyrecord_consumer_id = #{userId}\n" +
            "   AND sr.studyed_is_studyed = 1\n" +
            "   AND w.word_stage = #{stage}\n" +
            "   AND YEARWEEK(sr.studyrecord_time, 1) = YEARWEEK(CURDATE(), 1)) AS totalWords,\n" +
            "  \n" +
            "  (SELECT ROUND(\n" +
            "     (SELECT COUNT(DISTINCT sr.studyrecord_code) \n" +
            "      FROM studyrecord sr\n" +
            "      JOIN wordlist w ON sr.studyrecord_code = w.code\n" +
            "      WHERE sr.studyrecord_consumer_id = #{userId} \n" +
            "      AND sr.studyed_is_studyed = 1\n" +
            "      AND w.word_stage = #{stage}) * 100.0 / \n" +  // 移除时间限制
            "     (SELECT COUNT(DISTINCT code) \n" +
            "      FROM wordlist \n" +
            "      WHERE word_stage = #{stage}), \n" +
            "   2) \n" +
            "  ) AS courseComplete")  // 现在计算的是整个阶段的学习进度
    MyStudyProgressDto GetData(@Param("userId") Long userId, @Param("stage") Integer stage);


    /**
     * 用于获取用户每周的学习情况(按阶段)
     * @param userId 用户ID
     * @param stage 学习阶段
     * @return 每周学习数据
     */
    @Select("SELECT \n" +
            "  DATE(sr.studyrecord_time) AS studyDate, \n" +
            "  DAYNAME(sr.studyrecord_time) AS day, \n" +
            "  COUNT(DISTINCT sr.studyrecord_code) AS value \n" +
            "FROM studyrecord sr\n" +
            "JOIN wordlist w ON sr.studyrecord_code = w.code\n" +
            "WHERE sr.studyrecord_consumer_id = #{userId} \n" +
            "AND w.word_stage = #{stage}\n" +
            "AND YEARWEEK(sr.studyrecord_time, 1) = YEARWEEK(CURDATE(), 1) \n" +
            "GROUP BY DATE(sr.studyrecord_time), DAYNAME(sr.studyrecord_time)")
    List<WeeklyDataDao> getWeeklyStudyData(@Param("userId") Long userId, @Param("stage") Integer stage);


    /**
     * 用于更新用户的闯关的数据的sql实现
     * @param userId
     * @param currentLevel
     * @return
     */
    @Update("UPDATE consumer set current_level = #{currentLevel},consumer_score = consumer_score + 5 WHERE consumer_id = #{userId};")
    Integer SetUserLevel(@Param("userId") Long userId,@Param("currentLevel") Integer currentLevel);


    /**
     * 用于实现获取用户在某个阶段所需要学习的英语单词数据
     * @param userSatge
     * @param pageNo
     * @param pageSize
     * @return
     */
    List<WordDao> GetWord(@Param("userStage") Integer userSatge,@Param("pageNo") Integer pageNo, @Param("pageSize") Integer pageSize);


    /**
     * 用于实现向testrecord表中插入用户的测试数据
     * @param userId
     * @param testDataDto
     * @return
     */
    @Insert("<script>" +
            "INSERT INTO testrecord (" +
            "    testrecord_code, " +
            "    testrecord_is_true, " +
            "    testrecord_consumer_id, " +
            "    testrecord_time, " +
            "    user_answer, " +
            "    user_question, " +
            "    right_answer, " +
            "    testrecord_style, " +
            "    testrecord_is_mastered" +  // 新增字段
            ") " +
            "<choose>" +
            "    <when test='testDataDto.userAnswer.matches(\"\\\\d+\")'>" +  // 如果userAnswer是数字
            "        SELECT " +
            "            #{testDataDto.wordCode}, " +
            "            CASE WHEN #{testDataDto.isCorrect} = true THEN 1 ELSE 0 END, " +
            "            #{userId}, " +
            "            NOW(), " +
            "            w.interpretation, " +
            "            #{testDataDto.word}, " +
            "            #{testDataDto.correctAnswer}, " +
            "            0, " +
            "            CASE WHEN #{testDataDto.isCorrect} = true THEN 1 ELSE 0 END " +  // 根据testrecord_is_true更新testrecord_is_mastered
            "        FROM wordlist w " +
            "        WHERE w.code = #{testDataDto.userAnswer}" +
            "    </when>" +
            "    <otherwise>" +  // 如果userAnswer不是数字
            "        VALUES (" +
            "            #{testDataDto.wordCode}, " +
            "            CASE WHEN #{testDataDto.isCorrect} = true THEN 1 ELSE 0 END, " +
            "            #{userId}, " +
            "            NOW(), " +
            "            #{testDataDto.userAnswer}, " +
            "            #{testDataDto.word}, " +
            "            #{testDataDto.correctAnswer}, " +
            "            0, " +
            "            CASE WHEN #{testDataDto.isCorrect} = true THEN 1 ELSE 0 END " +  // 根据testrecord_is_true更新testrecord_is_mastered
            "        )" +
            "    </otherwise>" +
            "</choose>" +
            "</script>")
    Integer SubmitTest(@Param("userId") Long userId, @Param("testDataDto") TestDataDto testDataDto);


    /**
     * 用于获取某用户在某阶段的错题集数据
     * @param userId
     * @param userStage
     * @return
     */

    @Select("SELECT \n" +
            "    cr.checkrecord_code AS wordCode,\n" +
            "    w.word,\n" +
            "    w.phonetic,\n" +
            "    w.interpretation AS meaning,\n" +
            "    cr.user_answer AS wrongAnswer,\n" +
            "    cr.right_answer AS correctAnswer,\n" +
            "    cr.checkrecord_time AS time,\n" +
            "    CASE cr.checkrecord_style\n" +
            "        WHEN 0 THEN 'spelling'\n" +
            "        WHEN 1 THEN 'zhToEn'\n" +
            "        WHEN 2 THEN 'enToZh'\n" +
            "    END AS type,\n" +
            "    'daka' AS source,\n" +
            "    cr.checkrecord_is_mastered AS mastered,\n" +
            "    cr.user_question AS question\n" +
            "FROM checkrecord cr\n" +
            "JOIN wordlist w ON cr.checkrecord_code = w.code\n" +
            "WHERE cr.checkrecord_consumer_id = #{userId}\n" +
            "AND cr.checkrecord_is_true = 0\n" +
            "AND w.word_stage = #{userStage} " +
            " ORDER BY cr.checkrecord_time DESC;")
    List<CheckMistakesVo> GetCheckMistakes(@Param("userId") Long userId,@Param("userStage") Integer userStage);


    /**
     * 主要用于获取某用户某阶段的测试的错题集数据
     * @param userId
     * @param userStage
     * @return
     */


    @Select("SELECT \n" +
            "    tr.testrecord_code AS wordCode,\n" +
            "    w.word,\n" +
            "    w.phonetic,\n" +
            "    w.interpretation AS meaning,\n" +
            "    tr.user_answer AS wrongAnswer,\n" +
            "    tr.right_answer AS correctAnswer,\n" +
            "    tr.testrecord_time AS time,\n" +
            "    CASE tr.testrecord_style\n" +
            "        WHEN 0 THEN 'spelling'\n" +
            "        WHEN 1 THEN 'zhToEn'\n" +
            "        WHEN 2 THEN 'enToZh'\n" +
            "    END AS type,\n" +
            "    'test' AS source,\n" +
            "    tr.testrecord_is_mastered AS mastered,\n" +
            "    tr.user_question AS question\n" +
            "FROM testrecord tr\n" +
            "JOIN wordlist w ON tr.testrecord_code = w.code\n" +
            "WHERE tr.testrecord_consumer_id = #{userId}\n" +
            "AND tr.testrecord_is_true = 0\n" +
            "AND w.word_stage = #{userStage} " +
            "ORDER BY tr.testrecord_time DESC;")
    List<TestMistakesVo> GetTestMistakes(@Param("userId") Long userId,@Param("userStage") Integer userStage);


    /**
     * 用于实现获取单词详细信息的界面
     * @param wordCode
     * @return
     */
    @Select("select * from wordlist where code = #{wordCode};")
    WordDao GetWordDetail(@Param("wordCode") Long wordCode);


    /**
     * 用于获取打卡情况中已掌握的单词数量
     * @param userId
     * @param userStage
     * @return
     */
    @Select("SELECT COUNT(DISTINCT cr.checkrecord_code) AS mastered_count\n" +
            "FROM checkrecord cr\n" +
            "JOIN wordlist w ON cr.checkrecord_code = w.code\n" +
            "WHERE cr.checkrecord_consumer_id = #{userId}\n" +
            "  AND cr.checkrecord_is_mastered = 1\n" +
            "  AND cr.checkrecord_is_true = 0\n" +
            "  AND w.word_stage = #{userStage};")
    Long GetCheckMasterCount(@Param("userId") Long userId,@Param("userStage") Integer userStage);


    /**
     * 用于获取测试情况中已经掌握的单词数量
     * @param userId
     * @param userStage
     * @return
     */
    @Select("SELECT COUNT(DISTINCT tr.testrecord_code) AS mastered_count\n" +
            "FROM testrecord tr\n" +
            "JOIN wordlist w ON tr.testrecord_code = w.code\n" +
            "WHERE tr.testrecord_consumer_id = #{userId}\n" +
            "  AND tr.testrecord_is_mastered = 1\n" +
            "  AND tr.testrecord_is_true = 0\n" +
            "  AND w.word_stage = #{userStage};")
    Long GetTestMasterCount(@Param("userId") Long userId,@Param("userStage") Integer userStage);

    /**
     * 用于帮助用户对打卡错题集的更新
     * @param userId
     * @param wordCode
     * @param time
     * @return
     */
    @Update("UPDATE checkrecord " +
            "SET checkrecord_is_mastered = 1 " +
            "WHERE checkrecord_code = #{wordCode} " +
            "  AND checkrecord_consumer_id = #{userId} " +
            "  AND checkrecord_time = #{time}")
    Integer UpdateCheckMistakesMastered(@Param("userId") Long userId,
                                        @Param("wordCode") Long wordCode,
                                        @Param("time") String time);

    /**
     * 用于帮助用户测试错题集的更新
     * @param userId
     * @param wordCode
     * @param time
     * @return
     */
    @Update("UPDATE testrecord\n" +
            "SET testrecord_is_mastered = 1 \n" +
            "WHERE testrecord_code = #{wordCode}\n" +
            "  AND testrecord_consumer_id = #{userId}\n" +
            "  AND testrecord_time = #{time};")
    Integer UpdateTestMistakesMastered(@Param("userId") Long userId,@Param("wordCode") Long wordCode,@Param("time") String time);


    /**
     * 用于获取用户在其学习阶段的所有的单词数量
     * @param userStage
     * @return
     */
    @Select("Select count(*) from wordlist where word_stage = #{userStage};")
    Integer CountByStage(@Param("userStage") Integer userStage);


    /**
     * 用于获取某章节的学习数据
     * @param stage
     * @param offset
     * @param limit
     * @return
     */
    @Select("SELECT * FROM wordlist WHERE word_stage = #{stage} LIMIT #{limit} OFFSET #{offset};")
    List<WordDao> selectByStageWithLimit(@Param("stage") Integer stage, @Param("offset") Integer offset, @Param("limit") Integer limit);


    /**
     * 用于实现像后端数据库中插入默写记录数据
     * @param reciteChapterRecordDao
     * @return
     */
    @Insert("INSERT INTO english_study.recitechapterrecord " +
            "(user_id, chapter_index, total_words, correct_count, wrong_count, accuracy, start_time, end_time, duration, stage) " +
            "VALUES " +
            "(#{reciteChapterRecordDao.userId}, #{reciteChapterRecordDao.chapterIndex}, #{reciteChapterRecordDao.totalWords}, " +
            "#{reciteChapterRecordDao.correctCount}, #{reciteChapterRecordDao.wrongCount}, #{reciteChapterRecordDao.accuracy}, " +
            "#{reciteChapterRecordDao.startTime}, #{reciteChapterRecordDao.endTime}, #{reciteChapterRecordDao.duration}, " +
            "#{reciteChapterRecordDao.stage})")
    Integer InsertWriteRecord(@Param("reciteChapterRecordDao") ReciteChapterRecordDao reciteChapterRecordDao);

    /**
     * 用于获取用户默写的主键
     * @param userId
     * @param index
     * @param stage
     * @return
     */
    @Select("select record_id from recitechapterrecord where user_id = #{userId} AND chapter_index = #{index} AND stage = #{stage}; ")
    Long GetPrimaryKey(@Param("userId") Long userId,@Param("index") Integer index,@Param("stage") Integer stage);

    /**
     * 用于记录用户默写的单词详情
     * @param reciteDetailDao
     * @return
     */
    @Insert("INSERT INTO recitedetail " +
            "(record_id, word_id, word, interpretation, user_answer, is_correct) " +
            "VALUES " +
            "(#{reciteDetailDao.recordId}, #{reciteDetailDao.wordId}, #{reciteDetailDao.word}, " +
            "#{reciteDetailDao.interpretation}, #{reciteDetailDao.userAnswer}, " +
            "#{reciteDetailDao.isCorrect})")
    Integer InsertReciteDetail(@Param("reciteDetailDao") ReciteDetailDao reciteDetailDao);


    /**
     * 根据默写记录的主键获取该记录
     * @param recordId
     * @return
     */
    @Select("select * from recitechapterrecord where record_id = #{recordId};")
    ReciteChapterRecordDao GetReciteChapterRecord(@Param("recordId") Long recordId);


    //获取默写报告
    @Select("SELECT \n" +
            "    r.record_id AS recordId,\n" +
            "    r.chapter_index AS chapterIndex,\n" +
            "    r.total_words AS totalWords,\n" +
            "    r.correct_count AS correctCount,\n" +
            "    r.wrong_count AS wrongCount,\n" +
            "    r.accuracy AS accuracy,\n" +
            "    r.start_time AS startTime,\n" +
            "    r.end_time AS endTime,\n" +
            "    r.duration AS duration,\n" +
            "    r.stage AS stage\n" +
            "FROM \n" +
            "    recitechapterrecord r\n" +
            "WHERE \n" +
            "    r.record_id = #{recordId}\n" +
            "    AND r.user_id = #{userId};")
    WriteReportVo getReciteReport(@Param("userId") Long userId,@Param("recordId") Long recordId);


    //获取默写详情
    @Select("SELECT \n" +
            "    d.word_id AS wordId,\n" +
            "    d.word AS word,\n" +
            "    d.interpretation AS interpretation,\n" +
            "    d.user_answer AS userAnswer,\n" +
            "    d.is_correct AS isCorrect\n" +
            "FROM \n" +
            "    recitedetail d\n" +
            "WHERE \n" +
            "    d.record_id = #{recordId}\n" +
            "ORDER BY \n" +
            "    d.detail_id;")
    List<WriteReportDetailVo> getReciteDetails(@Param("recordId") Long recordId);


    //删除记录表
    @Delete("DELETE FROM recitechapterrecord WHERE record_id = #{recordId};")
    Integer DeleteReciteChapterRecordData(@Param("recordId") Long recordId);


    //删除详情表
    @Delete("DELETE FROM recitedetail WHERE record_id = #{recordId};")
    Integer DeleteReciteDetailData(@Param("recordId") Long recordId);


    /**
     * 用于实现用户利用积分兑换VIP的操作
     * @param consumerId
     * @return
     */
    @Update("UPDATE consumer set consumer_score = consumer_score - 5000, consumer_variety = 1 where consumer_id = #{consumerId};")

    Integer ExchangeVip(@Param("consumerId") Long consumerId);
}
